<?php
/**
 * database (mysql) class
 *
 * @author Sven Ruttloff, www.sruttloff.de <service@sruttloff.de>
 * @copyright S.Ruttloff, Computer Hard- & Software
 * @package core
 * @version 1
 *
 * @license http://www.sruttloff.de/cms/files/licence_en_lgpl.txt LGPL
 */

/**
 * DB Access to MySql
 *
 * @package core
 * @version 1
 */
class database
{
    /**
     * @var link id of connect
     */
    private $link_id = null;
    /**
     * @var result id from last request
     */
    var $result_id = null;
    /**
     * @var status of db connection
     */
    var $status = null;
    /**
     * @var is db open
     */
    var $open = false;
    /**
     * @var error text if request fails
     */
    var $errortxt;
    // Thanks to phpmyadmin team
    /**
     * @var object instance
     */
    private static $INSTANCE = array();
    /**
     * @var known functions for mysql
     */
    var $MYSQLFUNCTIONS = array (
        'ABS',
        'ACOS',
        'ADDDATE',
        'ADDTIME',
        'AES_DECRYPT',
        'AES_ENCRYPT',
        'AREA',
        'ASCII',
        'ASIN',
        'ATAN',
        'ATAN2',
        'AVG',
        'BENCHMARK',
        'BIN',
        'BIT_AND',
        'BIT_COUNT',
        'BIT_LENGTH',
        'BIT_OR',
        'BIT_XOR',
        'Boundary',
        'CAST',
        'CEIL',
        'CEILING',
        'CENTROID', // multipolygon-property-functions.html
        'CHAR', // string-functions.html
        'CHARACTER_LENGTH',
        'CHARSET', // information-functions.html
        'CHAR_LENGTH',
        'COALESCE',
        'COERCIBILITY', // information-functions.html
        'COLLATION', // information-functions.html
        'COMPRESS', // string-functions.html
        'CONCAT',
        'CONCAT_WS',
        'CONNECTION_ID',
        'CONV',
        'CONVERT',
        'CONVERT_TZ',
        'COS',
        'COT',
        'COUNT',
        'CRC32', // mathematical-functions.html
        'CURDATE',
        'CURRENT_DATE',
        'CURRENT_TIME',
        'CURRENT_TIMESTAMP',
        'CURRENT_USER',
        'CURTIME',
        'DATABASE',
        'DATE', // date-and-time-functions.html
        'DATEDIFF', // date-and-time-functions.html
        'DATE_ADD',
        'DATE_DIFF',
        'DATE_FORMAT',
        'DATE_SUB',
        'DAY',
        'DAYNAME',
        'DAYOFMONTH',
        'DAYOFWEEK',
        'DAYOFYEAR',
        'DECODE',
        'DEFAULT', // miscellaneous-functions.html
        'DEGREES',
        'DES_DECRYPT',
        'DES_ENCRYPT',
        'DIMENSION', // general-geometry-property-functions.html
        'ELT',
        'ENCODE',
        'ENCRYPT',
        'ENDPOINT', // linestring-property-functions.html
        'ENVELOPE', // general-geometry-property-functions.html
        'EXP',
        'EXPORT_SET',
        'EXTERIORRING', // polygon-property-functions.html
        'EXTRACT',
        'FIELD',
        'FIND_IN_SET',
        'FLOOR',
        'FORMAT',
        'FOUND_ROWS',
        'FROM_DAYS',
        'FROM_UNIXTIME',
        'GEOMETRYN', // geometrycollection-property-functions.html
        'GEOMETRYTYPE', // general-geometry-property-functions.html
        'GET_FORMAT',
        'GET_LOCK',
        'GLENGTH', // linestring-property-functions.html
        'GREATEST',
        'GROUP_CONCAT',
        'GROUP_UNIQUE_USERS',
        'HEX',
        'HOUR',
        'IF', //control-flow-functions.html
        'IFNULL',
        'INET_ATON',
        'INET_NTOA',
        'INSERT', // string-functions.html
        'INSTR',
        'INTERIORRINGN', // polygon-property-functions.html
        'INTERVAL',
        'ISCLOSED', // multilinestring-property-functions.html
        'ISEMPTY', // general-geometry-property-functions.html
        'ISNULL',
        'ISRING', // linestring-property-functions.html
        'ISSIMPLE', // general-geometry-property-functions.html
        'IS_FREE_LOCK',
        'IS_USED_LOCK', // miscellaneous-functions.html
        'LAST_DAY',
        'LAST_INSERT_ID',
        'LCASE',
        'LEAST',
        'LEFT',
        'LENGTH',
        'LN',
        'LOAD_FILE',
        'LOCALTIME',
        'LOCALTIMESTAMP',
        'LOCATE',
        'LOG',
        'LOG10',
        'LOG2',
        'LOWER',
        'LPAD',
        'LTRIM',
        'MAKEDATE',
        'MAKETIME',
        'MAKE_SET',
        'MASTER_POS_WAIT',
        'MAX',
        'MD5',
        'MICROSECOND',
        'MID',
        'MIN',
        'MINUTE',
        'MOD',
        'MONTH',
        'MONTHNAME',
        'NOW',
        'NULLIF',
        'NUMGEOMETRIES', // geometrycollection-property-functions.html
        'NUMINTERIORRINGS', // polygon-property-functions.html
        'NUMPOINTS', // linestring-property-functions.html
        'OCT',
        'OCTET_LENGTH',
        'OLD_PASSWORD',
        'ORD',
        'PASSWORD',
        'PERIOD_ADD',
        'PERIOD_DIFF',
        'PI',
        'POINTN', // linestring-property-functions.html
        'POINTONSURFACE', // multipolygon-property-functions.html
        'POSITION',
        'POW',
        'POWER',
        'QUARTER',
        'QUOTE',
        'RADIANS',
        'RAND',
        'RELEASE_LOCK',
        'REPEAT',
        'REPLACE', // string-functions.html
        'REVERSE',
        'RIGHT',
        'ROUND',
        'ROW_COUNT', // information-functions.html
        'RPAD',
        'RTRIM',
        'SCHEMA', // information-functions.html
        'SECOND',
        'SEC_TO_TIME',
        'SESSION_USER',
        'SHA',
        'SHA1',
        'SIGN',
        'SIN',
        'SLEEP', // miscellaneous-functions.html
        'SOUNDEX',
        'SPACE',
        'SQRT',
        'SRID', // general-geometry-property-functions.html
        'STARTPOINT', // linestring-property-functions.html
        'STD',
        'STDDEV',
        'STDDEV_POP', // group-by-functions.html
        'STDDEV_SAMP', // group-by-functions.html
        'STRCMP',
        'STR_TO_DATE',
        'SUBDATE',
        'SUBSTRING',
        'SUBSTRING_INDEX',
        'SUBTIME',
        'SUM',
        'SYSDATE',
        'SYSTEM_USER',
        'TAN',
        'TIME',
        'TIMEDIFF',
        'TIMESTAMP',
        'TIMESTAMPADD',
        'TIMESTAMPDIFF',
        'TIME_FORMAT',
        'TIME_TO_SEC',
        'TO_DAYS',
        'TRIM',
        'TRUNCATE', // mathematical-functions.html
        'UCASE',
        'UNCOMPRESS', // string-functions.html
        'UNCOMPRESSED_LENGTH', // string-functions.html
        'UNHEX', // string-functions.html
        'UNIQUE_USERS',
        'UNIX_TIMESTAMP',
        'UPPER',
        'USER',
        'UTC_DATE',
        'UTC_TIME',
        'UTC_TIMESTAMP',
        'UUID', // miscellaneous-functions.html
        'VARIANCE', // group-by-functions.html
        'VAR_POP', // group-by-functions.html
        'VAR_SAMP', // group-by-functions.html
        'VERSION',
        'WEEK',
        'WEEKDAY',
        'WEEKOFYEAR',
        'X', // point-property-functions.html
        'Y', // point-property-functions.html
        'YEAR',
        'YEARWEEK'
        );
        /**
         * instantiate
         * @return  void
         */
        private function __construct() { }
        /**
         * return a instance of object
         *
         * @param string session name (optional)
         * @return object instance
         */
        public static function getInstance($dbname = 'default')
        {
            if (!isset(self::$INSTANCE[$dbname]))
            {
                $c = __CLASS__;
                self::$INSTANCE[$dbname] = new $c();
            }
            return self::$INSTANCE[$dbname];
        }
        /**
         * Connect to db
         *
         * @param string hostname or IP
         * @param string username
         * @param string db name
         * @param string	password
         * @param bool suppress error trigger
         * @return bool succ. connect returns true
         */
        public function connect($host = "", $user = "", $name = "", $pass = "", $quiet = false)
        {
            if (!($this->link_id = @ mysql_pconnect($host, $user, $pass)))
            {
                $this->status = "<b>Database Server</b> Connection failed";
                return false;
            }
            if (!mysql_select_db($name, $this->link_id))
            {
                $this->status = "Database <em>$name</em> could not be selected!";
                if (!$quiet)
                trigger_error($this->status, E_USER_ERROR);
                return false;
            }
            $this->open = true;
            $this->query('SET CHARACTER SET "utf8"');
            return true;
        }
        /**
         * disconnect from DB
         *
         * @return	void
         */
        public function disconnect()
        {
            $this->open = false;
            mysql_close($this->link_id);
        }
        /**
         * Frees memory from last result id
         *
         * @return	void
         */
        function free()
        {
            if (isset ($this->result_id) && $this->result_id)
            {
                mysql_free_result($this->result_id);
                unset ($this->result_id);
            }
        }
        /**
         * Frees memory from result id
         *
         * @param	mixed	result id (from query)
         * @return	void
         */
        function free_by_id($resid)
        {
            mysql_free_result($resid);
        }
        /**
         * Send a query to existing db connection or create one (framework)
         *
         * @param	string    DB Query
         * @return	mixed 	 result id of query or false in error case
         */
        function query($query_string)
        {
            if (!$this->open)
            {
                $frontend = frontend::getInstance();
                if (!$this->connect($frontend->getConfig()->get("DBSERVER"), $frontend->getConfig()->get("DBUSER"), $frontend->getConfig()->get("DBBASE"), $frontend->getConfig()->get("DBPASS")))
                {
                    $fail[] = $this->status;
                }
            }
            $query_string = trim($query_string);
            $this->result_id = mysql_query($query_string, $this->link_id);
            if (!$this->result_id)
            {
                $this->errortxt = mysql_error();
                if (!$frontend->NEWERRORHANDLER) die("\n" . $query_string . "\n\n" . $this->errortxt);
                trigger_error("\n" . $query_string . "\n\n" . $this->errortxt, E_USER_ERROR);
                return false;
            }
            return $this->result_id;
        }
        /**
         * moves data pointer
         *
         * @param	int	 	target position of pointer
         * @param	mixed	link id
         * @return	bool
         */
        function move_pointer($i, $id = 0)
        {
            if ($id) return @ mysql_data_seek($id, $i);
            else return @ mysql_data_seek($this->result_id, $i);
        }
        /**
         * returns the quantity of result datasets from selects only!!!
         *
         * @param	mixed	link id
         * @return	int		number of rows
         */
        function num_rows($id = 0)
        {
            if ($id) return @ mysql_num_rows($id);
            else return @ mysql_num_rows($this->result_id);
        }
        /**
         * returns the quantity of result datasets from insert, update or delete
         *
         * @param	mixed	link id
         * @return	int		number of affected rows
         */
        function affected_rows()
        {
            return @ mysql_affected_rows($this->link_id);
        }
        /**
         * returns the quantity of result datasets (deprecated)
         *
         * @param	mixed   link id
         * @return	int		quantity of datas
         * @deprec
         */
        function num_rows_by_id($resid)
        {
            return mysql_num_rows($resid);
        }
        /**
         * fetch data result in a object
         *
         * @param	mixed   link id
         * @return	object   data object
         */
        function fetch($result_id = "")
        {
            if (!$result_id) return mysql_fetch_object($this->result_id);
            else return mysql_fetch_object($result_id);
        }
        /**
         * fetch data result as array
         *
         * @param	mixed   link id
         * @param	static	default MYSQL_BOTH
         * @return	array
         */
        function fetch_array($id = 0, $TYPE = MYSQL_BOTH)
        {
            if ($id) return mysql_fetch_array($id, $TYPE);
            else return mysql_fetch_array($this->result_id, $TYPE);
        }
        /**
         * fetch data result in a object
         *
         * @param	mixed   link id
         * @return	mixed
         * @deprec
         */
        function fetch_by_id($resid)
        {
            return mysql_fetch_object($resid, $this->link_id);
        }
        /**
         * returns the id (autoincrement) from last insert operation
         *
         * @return	int	last inserted auto incr. value
         */
        function insert_id()
        {
            return mysql_insert_id($this->link_id);
        }
        /**
         * returns error text from last operation
         *
         * @return	string		error text
         */
        function error()
        {
            return mysql_error();
        }
        /**
         * start a transaction
         * @return bool
         */
        public function transactionStart()
        {
            $query = 'begin';
            return $this->query($query);
        }
        /**
         * commit a transaction
         * @return bool
         */
        public function transactionCommit()
        {
            $query = 'commit';
            return $this->query($query);
        }
        /**
         * rollback a transaction
         * @return bool
         */
        public function transactionRollback()
        {
            $query = 'rollback';
            return $this->query($query);
        }
        /**
         * delete records from a db table
         *
         * @param      	string    	Tablename
         * @param      	string    	where
         * @param      	int    		data limit
         * @param		string		in case of join, you must decide table where to delete
         * @return     	int			number of affected rows
         */
        function DeleteRows($table = null, $where = null, $limit = null, $intable = "")
        {
            if (is_null($where)) $where = 1;
            if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table)) $table = '`' . $table . '`';
            $limitview = (string) null;
            if (!is_null($limit)) $limitview = ' LIMIT ' . $limit;
            $query = 'DELETE ' . $intable . ' FROM ' . $table . ' WHERE ' . $where . $limitview . ';';
            if (defined("DEBUG_QUERY")) trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
            return $this->affected_rows($this->query($query));
        }
        /**
         * Querie a db, return row as object
         *
         * @param	array	colums to select
         * @param	string   Tablename
         * @param	string   where
         * @param	string	order by col. names
         * @param	int    	data offset
         * @param	int   	data limit
         * @param	bool	return as array (default = false)
         * @return 	mixed	Object with results or array with objects
         */
        function SelectRows($columns = array (), $table = null, $where = null, $orderby = null, $offset = 0, $limit = null, $arraytype = false)
        {
            if (!$where) $where = 1; $limitview = (string) null;
            if (!is_null($limit)) $limitview = ' LIMIT ' . $offset . ',' . $limit;
            # secure column names
            foreach ($columns as $k => $v)
            {
                if (!preg_match('=^`.*`$=s', $v) && $v != '*' && !preg_match('=(\.|\sas\s|null|\(.*\))=si', $v)) $columns[$k] = '`' . $v . '`';
                else $columns[$k] = $v;
            }
            if ($orderby) $orderby = ' ORDER BY ' . $orderby;
            if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table)) $table = '`' . $table . '`';
            $query = 'SELECT ' . implode(',', $columns) . ' FROM ' . $table . ' WHERE ' . $where . $orderby . $limitview . ';';
            if (defined('DEBUG_QUERY') && DEBUG_QUERY == 1) trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
            if (!$result = $this->query($query)) return false;
            if ($this->num_rows($result) == 1 && !$arraytype) return $this->fetch($result);
            while ($row = $this->fetch($result)) $object[] = $row;
            return $object;
        }
        /**
         * Update datas in a db table
         *
         * @param	array	colums and values assoc array (FIELD='value')
         * @param	string	Tablename
         * @param	string	where
         * @param	int		number of dataset limit. e.g. 10
         * @param	string	dataset order by
         * @return	bool	true or false result
         */
        function UpdateRows($columns = array (), $table = null, $where = null, $limit = null, $orderby = null)
        {
            if (is_null($table) || is_null($where)) return false;
            if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table)) $table = '`' . $table . '`';
            $setquery = (string) '';
            # secure column names
            foreach ($columns as $k => $v)
            {
                if ($setquery) $setquery .= ', ';
                if (!preg_match('=^`.*`$=s', $k) && !preg_match('=(\.|as|\(.*\))=si', $k)) $setquery .= '`' . $k . '`=';
                else $setquery .= $k . '=';
                // is the value a mysql function
                $setquery .= is_null($v) ? 'NULL' : $this->IsMYSQLFunction($v);
            }
            $qlimit = $qorderby = (string) null;
            if (!is_null($limit)) $qlimit = ' LIMIT ' . $limit;
            if (!is_null($orderby)) $qorderby = ' ORDER BY ' . $orderby;
            $query = 'UPDATE ' . $table . ' SET ' . $setquery . ' WHERE ' . $where . $qorderby . $qlimit . ';';
            if (defined('DEBUG_QUERY') && DEBUG_QUERY == 1) trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
            return $this->query($query);
        }
        /**
         * check if cell value is a function
         *
         * @param	string		cell value
         * @return	string		quotet field value or unqu. if a function
         */
        function IsMYSQLFunction($v)
        {
            // already quoted
            if (preg_match("=^('|\").*('|\")$=", $v)) return substr($v, 1, (strlen($v)-2));
            if ($v == 'NULL') return $v;
            // is value with brackets
            if (preg_match('=^.+\(.*\).*$=', $v))
            {
                // is the value a mysql function
                if (!in_array(preg_replace('=\(.*\)=si', '', $v), $this->MYSQLFUNCTIONS)) return "'" . $v . "'";
                else return $v;
            }
            return "'" . $v . "'";
        }
        /**
         * insert a row in a db table
         *
         * @param	array	colums and values assoc array (FIELD='value')
         * @param	string	Tablename
         * @return	bool	true or false result
         */
        function InsertRow($columns = array (), $table = null)
        {
            if (is_null($table)) return false;
            if (!preg_match('=^`.*`$=s', $table) && !preg_match('=(\.|as|\(.*\))=si', $table)) $table = '`' . $table . '`';
            $setquery = (string) '';
            # secure column names
            foreach ($columns as $k => $v)
            {
                if ($setquery) $setquery .= ', ';
                if (!preg_match('=^`.*`$=s', $k) && !preg_match('=(\.|\sas\s|null|\(.*\))=si', $k) && $v != 'NULL' && !is_null($v))
                {
                    $setquery .= '`' . $k . '`=';
                }
                else $setquery .= $k . '=';
                $setquery .= $this->IsMYSQLFunction($v);
            }
            $query = 'INSERT INTO ' . $table . ' SET ' . $setquery . ';';
            if (defined("DEBUG_QUERY") && DEBUG_QUERY == 1) trigger_error("\n" . $query . "\n\n!!! DEBUGING Enabled !!!", E_USER_ERROR);
            return $this->query($query);
        }
        /**
         * returns a fulltext search with like statement
         *
         * @param	array	column names where to look in
         * @param	string   + and / or - words to look for
         * @return	string	statement for use in where clause
         */
        function BuildFullTextQuery($columns = array (), $text = null)
        {
            $searchquery = null;
            $searchstring = array ();
            $pattern = '=([\+\-]?".+")=siU';
            $text = trim(stripslashes($text));
            while (preg_match($pattern, $text, $searchstring2))
            {
                $text = preg_replace('=' . preg_quote($searchstring2[1]) . '=siU', '', $text);
                unset ($searchstring2[0]);
                $searchstring = array_merge($searchstring, $searchstring2);
            }
            if (!$searchwords = explode(' ', trim($text))) $searchwords = $text ? array (trim($text)) : null;
            // combine
            if (is_array($searchstring))
            {
                $searchstring = str_replace('"', '', $searchstring);
                if (is_array($searchwords)) $searchwords = array_merge($searchwords, $searchstring);
                else $searchwords = $searchstring;
            }
            if (is_array($columns)) $searchrows = $columns;
            else $searchrows = array ($columns);
            // Analyse
            $first = false;
            // take care to be converted as string
            foreach ($columns as $k=>$v) $columns[$k] = "CAST(".$v." AS CHAR)";
            // concat searchrows
            $concat = "CONCAT_WS(' '," . implode(",", $columns) . ")";
            $searchquery .= " (";
            $first = false;
            for ($x = 0; $x < count($searchwords); $x++)
            {
                if (!$searchwords[$x]) continue;
                if (!$first)
                {
                    $first = true;
                    //erstes Wort?
                    switch (substr($searchwords[$x], 0, 1))
                    {
                        case "-" :
                            $searchquery .= $concat . " NOT Like '%" . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                            break;
                        case "+" :
                            $searchquery .= $concat . " Like '%" . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                            break;
                        default :
                            $searchquery .= $concat . " Like '%$searchwords[$x]%' ";
                            break;
                    }
                }
                else
                {
                    switch (substr($searchwords[$x], 0, 1))
                    {
                        case "-" :
                            $searchquery .= "AND " . $concat ." NOT Like '%" . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                            break;
                        case "+" :
                            $searchquery .= "AND " . $concat . " Like '%" . substr($searchwords[$x], 1, strlen($searchwords[$x])) . "%' ";
                            break;
                        default :
                            $searchquery .= "OR " . $concat . " Like '%$searchwords[$x]%' ";
                            break;
                    }
                }
            }
            $searchquery .= ") ";
            return $searchquery == " () " ? " 1 " : $searchquery;
        }
}